using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections;
using BE;

namespace DAL//ModulDuoc.BusinessLayer.DataLayer

{

	/// <summary>
	/// Data access layer class for ZD_DM_THUOC
	/// </summary>
	public class ZD_DM_THUOCSql : DataLayerBase 
	{

        #region Constructor

		/// <summary>
		/// Class constructor
		/// </summary>
		public ZD_DM_THUOCSql()
		{
			// Nothing for now.
		}

        #endregion

        #region Public Methods

        /// <summary>
        /// insert new row in the table
        /// </summary>
		/// <param name="businessObject">business object</param>
		/// <returns>true of successfully insert</returns>
		public bool Insert(ZD_DM_THUOC businessObject)
		{
			SqlCommand	sqlCommand = new SqlCommand();
			sqlCommand.CommandText = "dbo.[zD_DM_THUOC_Insert]";
			sqlCommand.CommandType = CommandType.StoredProcedure;

			// Use connection object of base class
			sqlCommand.Connection = MainConnection;

			try
			{
                
				sqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.BigInt, 8, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Proposed, businessObject.ID));
				sqlCommand.Parameters.Add(new SqlParameter("@MAVT", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.MAVT));
				sqlCommand.Parameters.Add(new SqlParameter("@MAHANGHOA", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.MAHANGHOA));
				sqlCommand.Parameters.Add(new SqlParameter("@TENVT", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TENVT));
				sqlCommand.Parameters.Add(new SqlParameter("@MANHOMDV", SqlDbType.NVarChar, 30, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.MANHOMDV));
				sqlCommand.Parameters.Add(new SqlParameter("@MANHOMVATTU", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.MANHOMVATTU));
				sqlCommand.Parameters.Add(new SqlParameter("@DVT", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.DVT));
				sqlCommand.Parameters.Add(new SqlParameter("@THUOCTINHDONGGOI", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.THUOCTINHDONGGOI));
				sqlCommand.Parameters.Add(new SqlParameter("@MANSX", SqlDbType.NVarChar, 30, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.MANSX));
				sqlCommand.Parameters.Add(new SqlParameter("@TONTOIDA", SqlDbType.Float, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TONTOIDA));
				sqlCommand.Parameters.Add(new SqlParameter("@TONTOITHIEU", SqlDbType.Float, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TONTOITHIEU));
				sqlCommand.Parameters.Add(new SqlParameter("@VITRILUUTRU", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.VITRILUUTRU));
				sqlCommand.Parameters.Add(new SqlParameter("@GHICHU", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.GHICHU));
				sqlCommand.Parameters.Add(new SqlParameter("@CHINHANH_DB", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.CHINHANH_DB));
				sqlCommand.Parameters.Add(new SqlParameter("@NHACC", SqlDbType.NVarChar, 30, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.NHACC));
				sqlCommand.Parameters.Add(new SqlParameter("@Infoinclude", SqlDbType.NVarChar, 500, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Infoinclude));
				sqlCommand.Parameters.Add(new SqlParameter("@Usage", SqlDbType.NVarChar, 500, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Usage));
				sqlCommand.Parameters.Add(new SqlParameter("@NameSearch", SqlDbType.NVarChar, 500, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.NameSearch));
                sqlCommand.Parameters.Add(new SqlParameter("@NhietDo", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.NhietDo));
                sqlCommand.Parameters.Add(new SqlParameter("@AnhSang", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.AnhSang));
                sqlCommand.Parameters.Add(new SqlParameter("@TamTay", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TamTay));
                sqlCommand.Parameters.Add(new SqlParameter("@AmUot", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.AmUot));
                sqlCommand.Parameters.Add(new SqlParameter("@HuongThan", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.HuongThan));
                sqlCommand.Parameters.Add(new SqlParameter("@BanTheoDon", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.BanTheoDon));
                sqlCommand.Parameters.Add(new SqlParameter("@TenNSX", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TenNSX));
                sqlCommand.Parameters.Add(new SqlParameter("@ThuocBH", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.ThuocBH));
                sqlCommand.Parameters.Add(new SqlParameter("@Field1", SqlDbType.NText, 1073741823, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Field1));
                sqlCommand.Parameters.Add(new SqlParameter("@Field2", SqlDbType.NText, 1073741823, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Field2));
                sqlCommand.Parameters.Add(new SqlParameter("@Field3", SqlDbType.NText, 1073741823, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Field3));
								
				MainConnection.Open();
				
				sqlCommand.ExecuteNonQuery();
                businessObject.ID = (long)sqlCommand.Parameters["@ID"].Value;

				return true;
			}
			catch(Exception ex)
			{				
				throw new Exception("ZD_DM_THUOC::Insert::Error occured.", ex);
			}
			finally
			{			
				MainConnection.Close();
				sqlCommand.Dispose();
			}
		}

         /// <summary>
        /// update row in the table
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <returns>true for successfully updated</returns>
        public bool Update(ZD_DM_THUOC businessObject)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[zD_DM_THUOC_Update]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
				sqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.BigInt, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.ID));
				sqlCommand.Parameters.Add(new SqlParameter("@MAVT", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.MAVT));
				sqlCommand.Parameters.Add(new SqlParameter("@MAHANGHOA", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.MAHANGHOA));
				sqlCommand.Parameters.Add(new SqlParameter("@TENVT", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TENVT));
				sqlCommand.Parameters.Add(new SqlParameter("@MANHOMDV", SqlDbType.NVarChar, 30, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.MANHOMDV));
				sqlCommand.Parameters.Add(new SqlParameter("@MANHOMVATTU", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.MANHOMVATTU));
				sqlCommand.Parameters.Add(new SqlParameter("@DVT", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.DVT));
				sqlCommand.Parameters.Add(new SqlParameter("@THUOCTINHDONGGOI", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.THUOCTINHDONGGOI));
				sqlCommand.Parameters.Add(new SqlParameter("@MANSX", SqlDbType.NVarChar, 30, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.MANSX));
				sqlCommand.Parameters.Add(new SqlParameter("@TONTOIDA", SqlDbType.Float, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TONTOIDA));
				sqlCommand.Parameters.Add(new SqlParameter("@TONTOITHIEU", SqlDbType.Float, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TONTOITHIEU));
				sqlCommand.Parameters.Add(new SqlParameter("@VITRILUUTRU", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.VITRILUUTRU));
				sqlCommand.Parameters.Add(new SqlParameter("@GHICHU", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.GHICHU));
				sqlCommand.Parameters.Add(new SqlParameter("@CHINHANH_DB", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.CHINHANH_DB));
				sqlCommand.Parameters.Add(new SqlParameter("@NHACC", SqlDbType.NVarChar, 30, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.NHACC));
				sqlCommand.Parameters.Add(new SqlParameter("@Infoinclude", SqlDbType.NVarChar, 500, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Infoinclude));
				sqlCommand.Parameters.Add(new SqlParameter("@Usage", SqlDbType.NVarChar, 500, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Usage));
				sqlCommand.Parameters.Add(new SqlParameter("@NameSearch", SqlDbType.NVarChar, 500, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.NameSearch));
                sqlCommand.Parameters.Add(new SqlParameter("@NhietDo", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.NhietDo));
                sqlCommand.Parameters.Add(new SqlParameter("@AnhSang", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.AnhSang));
                sqlCommand.Parameters.Add(new SqlParameter("@TamTay", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TamTay));
                sqlCommand.Parameters.Add(new SqlParameter("@AmUot", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.AmUot));
                sqlCommand.Parameters.Add(new SqlParameter("@HuongThan", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.HuongThan));
                sqlCommand.Parameters.Add(new SqlParameter("@BanTheoDon", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.BanTheoDon));
                sqlCommand.Parameters.Add(new SqlParameter("@TenNSX", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.TenNSX));
                sqlCommand.Parameters.Add(new SqlParameter("@ThuocBH", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.ThuocBH));
                sqlCommand.Parameters.Add(new SqlParameter("@Field1", SqlDbType.NText, 1073741823, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Field1));
                sqlCommand.Parameters.Add(new SqlParameter("@Field2", SqlDbType.NText, 1073741823, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Field2));
                sqlCommand.Parameters.Add(new SqlParameter("@Field3", SqlDbType.NText, 1073741823, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Field3));
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("ZD_DM_THUOC::Update::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }

        /// <summary>
        /// Delete by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>true for successfully deleted</returns>
        public bool Delete(ZD_DM_THUOCKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[zD_DM_THUOC_DeleteByPrimaryKey]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

				sqlCommand.Parameters.Add(new SqlParameter("@MAVT", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.MAVT));


                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("ZD_DM_THUOC::DeleteByKey::Error occured.", ex);
            }
            finally
            {                
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Delete records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>true for successfully deleted</returns>
        public bool DeleteByField(string fieldName, object value)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[zD_DM_THUOC_DeleteByField]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@FieldName", fieldName));
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));
                
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;

            }
            catch (Exception ex)
            {                
                throw new Exception("ZD_DM_THUOC::DeleteByField::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }
        /// <summary>
        /// Select by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>ZD_DM_THUOC business object</returns>
        public ZD_DM_THUOC SelectByPrimaryKey(ZD_DM_THUOCKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[zD_DM_THUOC_SelectByPrimaryKey]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

				sqlCommand.Parameters.Add(new SqlParameter("@MAVT", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.MAVT));

                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                if (dataReader.Read())
                {
                    ZD_DM_THUOC businessObject = new ZD_DM_THUOC();

                    PopulateBusinessObjectFromReader(businessObject, dataReader);

                    return businessObject;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("ZD_DM_THUOC::SelectByPrimaryKey::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }


        /// <summary>
        /// Select all rescords
        /// </summary>
        /// <returns>list of ZD_DM_THUOC</returns>
        public List<ZD_DM_THUOC> SelectAll_List()
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[zD_DM_THUOC_SelectAll]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {                
                throw new Exception("ZD_DM_THUOC::SelectAll::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Select records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>list of ZD_DM_THUOC</returns>
        public List<ZD_DM_THUOC> SelectByField_List(string fieldName, object value)
        {

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[zD_DM_THUOC_SelectByField]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@FieldName", fieldName));
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));

                MainConnection.Open();
                
                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {
                throw new Exception("ZD_DM_THUOC::SelectByField::Error occured.", ex);
            }
            finally
            {

                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

//thêm hiennv

        /// <summary>
        /// Select all rescords
        /// </summary>
        /// <returns>dataTable</returns>
        public DataTable SelectAll()
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[zD_DM_THUOC_SelectAll]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
                MainConnection.Open();

                DataTable ds = new DataTable();
                SqlDataAdapter myadapter = new SqlDataAdapter(sqlCommand);
                myadapter.Fill(ds);

                return ds;

            }
            catch (Exception ex)
            {                
                throw new Exception("ZD_DM_THUOC::SelectAll::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }
        /// <summary>
        /// Select all rescords
        /// </summary>
        /// <returns>dataTable</returns>
        public DataTable Select_Thuoc_NhapKhoCT()
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[zD_DM_THUOC_NhapKhoCT]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                MainConnection.Open();

                DataTable ds = new DataTable();
                SqlDataAdapter myadapter = new SqlDataAdapter(sqlCommand);
                myadapter.Fill(ds);

                return ds;

            }
            catch (Exception ex)
            {
                throw new Exception("hien_Thuoc_PhieuNhapCT::SelectAll::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Select all rescords
        /// </summary>
        /// <returns>dataTable</returns>
        public DataTable Select_TonKhoThuoc(string mavach)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[TONKHO_THUOC_KeDon]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                sqlCommand.Parameters.Add(new SqlParameter("@Mavach", mavach));
                MainConnection.Open();

                DataTable ds = new DataTable();
                SqlDataAdapter myadapter = new SqlDataAdapter(sqlCommand);
                myadapter.Fill(ds);

                return ds;

            }
            catch (Exception ex)
            {
                throw new Exception("TONKHO_THUOC_KeDon::SelectAll::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }
        public DataTable Search(string value)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[zD_DM_THUOC_Search]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));
                MainConnection.Open();

                DataTable ds = new DataTable();
                SqlDataAdapter myadapter = new SqlDataAdapter(sqlCommand);
                myadapter.Fill(ds);

                return ds;

            }
            catch (Exception ex)
            {
                throw new Exception("ZD_DM_THUOC::Search::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }
        public DataTable Search_BietDuoc(string value)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[zD_DM_THUOC_Search-BietDuoc]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));
                MainConnection.Open();

                DataTable ds = new DataTable();
                SqlDataAdapter myadapter = new SqlDataAdapter(sqlCommand);
                myadapter.Fill(ds);

                return ds;

            }
            catch (Exception ex)
            {
                throw new Exception("ZD_DM_THUOC-BietDuoc::Search::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Select records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>dataTable</returns>
        public DataTable SelectByField(string fieldName, object value)
        {

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[zD_DM_THUOC_SelectByField]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@FieldName", fieldName));
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));

                MainConnection.Open();
                
                DataTable ds = new DataTable();
                SqlDataAdapter myadapter = new SqlDataAdapter(sqlCommand);
                myadapter.Fill(ds);

                return ds;

            }
            catch (Exception ex)
            {
                throw new Exception("ZD_DM_THUOC::SelectByField::Error occured.", ex);
            }
            finally
            {

                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }     
//end thêm hiennv
        #endregion

        #region Private Methods

        /// <summary>
        /// Populate business object from data reader
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <param name="dataReader">data reader</param>
        internal void PopulateBusinessObjectFromReader(ZD_DM_THUOC businessObject, IDataReader dataReader)
        {


				businessObject.ID = dataReader.GetInt64(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.ID.ToString()));

				businessObject.MAVT = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.MAVT.ToString()));

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.MAHANGHOA.ToString())))
				{
					businessObject.MAHANGHOA = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.MAHANGHOA.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.TENVT.ToString())))
				{
					businessObject.TENVT = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.TENVT.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.MANHOMDV.ToString())))
				{
					businessObject.MANHOMDV = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.MANHOMDV.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.MANHOMVATTU.ToString())))
				{
					businessObject.MANHOMVATTU = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.MANHOMVATTU.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.DVT.ToString())))
				{
					businessObject.DVT = dataReader.GetInt32(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.DVT.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.THUOCTINHDONGGOI.ToString())))
				{
					businessObject.THUOCTINHDONGGOI = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.THUOCTINHDONGGOI.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.MANSX.ToString())))
				{
					businessObject.MANSX = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.MANSX.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.TONTOIDA.ToString())))
				{
					businessObject.TONTOIDA = dataReader.GetDouble(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.TONTOIDA.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.TONTOITHIEU.ToString())))
				{
					businessObject.TONTOITHIEU = dataReader.GetDouble(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.TONTOITHIEU.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.VITRILUUTRU.ToString())))
				{
					businessObject.VITRILUUTRU = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.VITRILUUTRU.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.GHICHU.ToString())))
				{
					businessObject.GHICHU = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.GHICHU.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.CHINHANH_DB.ToString())))
				{
					businessObject.CHINHANH_DB = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.CHINHANH_DB.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.NHACC.ToString())))
				{
					businessObject.NHACC = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.NHACC.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.Infoinclude.ToString())))
				{
					businessObject.Infoinclude = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.Infoinclude.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.Usage.ToString())))
				{
					businessObject.Usage = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.Usage.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.NameSearch.ToString())))
				{
					businessObject.NameSearch = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.NameSearch.ToString()));
				}
                if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.TenNSX.ToString())))
                {
                    businessObject.TenNSX = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.TenNSX.ToString()));
                }
                if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.Field1.ToString())))
                {
                    businessObject.Field1 = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.Field1.ToString()));
                }
                if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.Field2.ToString())))
                {
                    businessObject.Field2 = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.Field2.ToString()));
                }
                if (!dataReader.IsDBNull(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.Field3.ToString())))
                {
                    businessObject.Field3 = dataReader.GetString(dataReader.GetOrdinal(ZD_DM_THUOC.ZD_DM_THUOCFields.Field3.ToString()));
                }

        }

        /// <summary>
        /// Populate business objects from the data reader
        /// </summary>
        /// <param name="dataReader">data reader</param>
        /// <returns>list of ZD_DM_THUOC</returns>
        internal List<ZD_DM_THUOC> PopulateObjectsFromReader(IDataReader dataReader)
        {

            List<ZD_DM_THUOC> list = new List<ZD_DM_THUOC>();

            while (dataReader.Read())
            {
                ZD_DM_THUOC businessObject = new ZD_DM_THUOC();
                PopulateBusinessObjectFromReader(businessObject, dataReader);
                list.Add(businessObject);
            }
            return list;

        }

        #endregion

	}
}
